package com.hyperzsoft.budgeteer;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class BudgetDBProvider {
	OpenHelper openHelper = null;
	
	private static final String DATABASE_NAME = "onabudget.db";
	private static final int DATABASE_VERSION = 2;
	private static final String TAG = "BudgetDBProvider";

	private static SQLiteDatabase budgetDB;

	private static final String EXPENSE_TABLE_NAME = "expense";
	private static final String MONTHLY_TABLE_NAME = "monthly";
	private static final String CREDIT_TABLE_NAME = "credit";

	private static String[] EXPENSE_SELECT_COLS;
	private static String[] MONTHLY_SELECT_COLS;
	private static String[] CREDIT_SELECT_COLS;

	private static String CREDIT_SELECT_SORT = "_id asc";
	private static String EXPENSE_SELECT_SORT = "_id asc";
	private static String MONTHLY_SELECT_SORT = "_id asc";

	private Context context;
	private SQLiteStatement insertStmt;

	private String EXPENSE_INSERT;
	private String MONTHLY_INSERT;
	private String CREDIT_INSERT;
	
	public enum BudgetTable {
		CREDIT (CREDIT_TABLE_NAME, 
				CREDIT_SELECT_SORT),
				
		EXPENSE (EXPENSE_TABLE_NAME, 
				EXPENSE_SELECT_SORT),

		MONTHLY (MONTHLY_TABLE_NAME, 
				MONTHLY_SELECT_SORT);

		private BudgetTable(String aTable, String aSort) {
			//can't call super ctor here
			fTable = aTable;
			fSort = aSort;
		}
		
		final String getSort() {
			return fSort;
		}
		
		final String getTable() {
			return fTable;
		}

		private final String fSort;
		private final String fTable;
	}

	public BudgetDBProvider(Context context) {
		try {
			this.context = context;
			openHelper = new OpenHelper(this.context);
			budgetDB = openHelper.getWritableDatabase();
			
			initGlobals();
			
			
		} catch (SQLException e) {
			Log.e(TAG, e.getMessage());
			Log.e(TAG, e.getStackTrace().toString());
		}
	}
	
	public void close() {
	    if (openHelper != null) {
	        openHelper.close();
	    }
	}
	
	private void initGlobals() {
		EXPENSE_SELECT_COLS = new String[] { "_id",
			this.context.getString(R.string.expense_name),
			this.context.getString(R.string.expense_type),
			this.context.getString(R.string.expense_amount),
			this.context.getString(R.string.expense_amount_type) };
		
		MONTHLY_SELECT_COLS = new String[] { "_id",
			this.context.getString(R.string.monthly_item_expense_id),
			this.context.getString(R.string.monthly_item_credit_id),
			this.context.getString(R.string.monthly_item_due_date),
			this.context.getString(R.string.monthly_item_balance),
			this.context.getString(R.string.monthly_item_amount_paid) };
		
		CREDIT_SELECT_COLS = new String[] { "_id",
			this.context.getString(R.string.credit_amount),
			this.context.getString(R.string.credit_date) };

		EXPENSE_INSERT = "INSERT INTO " + EXPENSE_TABLE_NAME + " ("
			+ this.context.getString(R.string.expense_name) + ","
			+ this.context.getString(R.string.expense_type) + ","
			+ this.context.getString(R.string.expense_amount) + ","
			+ this.context.getString(R.string.expense_amount_type) + ")"
			+ " VALUES (?,?,?,?)";

		MONTHLY_INSERT = "INSERT INTO " + MONTHLY_TABLE_NAME + " ("
			+ this.context.getString(R.string.monthly_item_expense_id) + ","
			+ this.context.getString(R.string.monthly_item_credit_id) + ","
			+ this.context.getString(R.string.monthly_item_due_date) + ","
			+ this.context.getString(R.string.monthly_item_balance)  + ","
			+ this.context.getString(R.string.monthly_item_amount_paid) + ")"
			+ " VALUES (?,?,?,?,?)";

		CREDIT_INSERT = "INSERT INTO " + CREDIT_TABLE_NAME + " ("
			+ this.context.getString(R.string.credit_amount) + ","
			+ this.context.getString(R.string.credit_date) + ")"
			+ " VALUES (?,?)";
	}
	
	public long insert(Expense expense) {
		try {
			this.insertStmt = budgetDB.compileStatement(EXPENSE_INSERT);

			this.insertStmt.bindString(1, expense.NAME);
			this.insertStmt.bindString(2, expense.TYPE);
			this.insertStmt.bindLong(3, (long) expense.AMOUNT);
			this.insertStmt.bindString(4, expense.AMOUNT_TYPE);

			return this.insertStmt.executeInsert();
			
		} catch (SQLException e) {
			Log.e(TAG, e.getLocalizedMessage());
			Log.e(TAG, e.getStackTrace().toString());
			return -1;
		}
	}
	
	public long insert(Credit credit) {
		try {
			this.insertStmt = budgetDB.compileStatement(CREDIT_INSERT);

			this.insertStmt.bindLong(1, credit.AMOUNT);
			this.insertStmt.bindLong(2, credit.CREDIT_DATE);

			return this.insertStmt.executeInsert();
		} catch (SQLException e) {
			Log.e(TAG, e.getLocalizedMessage());
			Log.e(TAG, e.getStackTrace().toString());
			return -1;
		}
	}

	public long insert(Monthly monthly) {
		try {
			this.insertStmt = budgetDB.compileStatement(MONTHLY_INSERT);

			this.insertStmt.bindLong(1, monthly.EXPENSE_ID);
			this.insertStmt.bindLong(2, monthly.CREDIT_ID);
			this.insertStmt.bindLong(3, monthly.DUE_DATE);
			this.insertStmt.bindLong(4, (long) monthly.BALANCE);

			return this.insertStmt.executeInsert();
		} catch (SQLException e) {
			Log.e(TAG, e.getLocalizedMessage());
			Log.e(TAG, e.getStackTrace().toString());
			return -1;
		}
	}

	public void delete(BudgetTable db_table, int rowID) { 
		budgetDB.delete(db_table.getTable(), "_id=" + rowID, null);
	}
	
	public void deleteAll(BudgetTable db_table) {
		budgetDB.delete(db_table.getTable(), null, null);
	}
	
	public ArrayList<Object> loadObjectList(BudgetTable db_table, Cursor cursor) {
		//NOTE: Is there a better way to do this instead of instantiating
		// all three objs?? Look into using interfaces and instanceOf. 
		Expense expenseObj;
		Credit creditObj;
		Monthly monthlyObj;
		ArrayList<Object> list = new ArrayList<Object>();
		
		try {
			if (cursor.moveToFirst()) {
				do {
					switch (db_table) {
					case EXPENSE:
						expenseObj = new Expense();
						expenseObj._ID = (int) cursor.getLong(0);
						expenseObj.NAME = cursor.getString(1);
						expenseObj.TYPE = cursor.getString(2);
						expenseObj.AMOUNT = (double) cursor.getLong(3);
						expenseObj.AMOUNT_TYPE = cursor.getString(4);

						list.add(expenseObj);
						break;
					case CREDIT:
						creditObj = new Credit();
						creditObj._ID = (int) cursor.getLong(0);
						creditObj.CREDIT_DATE = cursor.getLong(2);
						creditObj.AMOUNT = cursor.getLong(1);

						list.add(creditObj);
						break;
					case MONTHLY:
						monthlyObj = new Monthly();
						monthlyObj._ID = (int) cursor.getLong(0);
						monthlyObj.EXPENSE_ID = (int) cursor.getLong(1);
						monthlyObj.CREDIT_ID = (int) cursor.getLong(2);
						monthlyObj.DUE_DATE = cursor.getLong(3);
						monthlyObj.AMOUNT_PAID = (double) cursor.getLong(4);
						monthlyObj.BALANCE = (double) cursor.getLong(5);

						list.add(monthlyObj);
						break;
					default:
						throw new IllegalArgumentException("Invalid or NULL Database Table");
					}
				} while (cursor.moveToNext());
			}
		} catch (SQLException e) {
			Log.e(TAG, e.getMessage());
			Log.e(TAG, e.getStackTrace().toString());
		}
		
		return list;
	}
	
	public ArrayList<Object> selectAll(BudgetTable db_table) {
		Cursor cursor;
		String[] table_cols;
		ArrayList<Object> returnList = new ArrayList<Object>();
		
		try {
			switch (db_table) {
				case EXPENSE:
					table_cols = EXPENSE_SELECT_COLS;
					break;
				case CREDIT:
					table_cols = CREDIT_SELECT_COLS;
					break;
				case MONTHLY:
					table_cols = MONTHLY_SELECT_COLS;
					break;
				default:
					throw new IllegalArgumentException("Invalid or NULL Database Table");
			}
			
			cursor = budgetDB.query(db_table.getTable(), table_cols, 
					null, null, null, null, db_table.getSort());
			
			ArrayList<Object> list = loadObjectList(db_table, cursor);
			
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			
			//damn scope, again a better way to do this? I need to return 
			// that list but different items need to be set to load the list.
			returnList = list;
		} catch (SQLException e) {
			Log.e(TAG, e.getMessage());
			Log.e(TAG, e.getStackTrace().toString());
		}
		
		return returnList;
	}
	
	@SuppressWarnings("unused")
	public class OpenHelper extends SQLiteOpenHelper {
		private static final String TAG = "OpenHelper";
		private Context context;

		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			this.context = context;
		}

		@Override
		public void onCreate(SQLiteDatabase budgetDB) {
			
			//Create all the tables
			budgetDB.execSQL("CREATE TABLE " + EXPENSE_TABLE_NAME + " ("
					+ "_id INTEGER PRIMARY KEY,"
					+ this.context.getString(R.string.expense_name) + " TEXT,"
					+ this.context.getString(R.string.expense_type) + " TEXT,"
					+ this.context.getString(R.string.expense_amount) + " INTEGER,"
					+ this.context.getString(R.string.expense_amount_type) + " TEXT"
					+ ");");

			budgetDB.execSQL("CREATE TABLE " + CREDIT_TABLE_NAME + " ("
					+ "_id INTEGER PRIMARY KEY,"
					+ this.context.getString(R.string.credit_date) + " INTEGER,"
					+ this.context.getString(R.string.credit_amount) + " INTEGER"
					+ ");");

			budgetDB.execSQL("CREATE TABLE " + MONTHLY_TABLE_NAME + " ("
					+ "_id INTEGER PRIMARY KEY,"
					+ this.context.getString(R.string.monthly_item_expense_id) + " INTEGER,"
					+ this.context.getString(R.string.monthly_item_credit_id) + " INTEGER,"
					+ this.context.getString(R.string.monthly_item_due_date) + " INTEGER,"
					+ this.context.getString(R.string.monthly_item_amount_paid) + " INTEGER,"
					+ this.context.getString(R.string.monthly_item_balance) + " INTEGER"
					+ ");");
		}

		@Override
		public void onUpgrade(SQLiteDatabase budgetDB, int oldVersion, int newVersion) {
			budgetDB.execSQL("DROP TABLE IF EXISTS " + EXPENSE_TABLE_NAME);
			budgetDB.execSQL("DROP TABLE IF EXISTS " + CREDIT_TABLE_NAME);
			budgetDB.execSQL("DROP TABLE IF EXISTS " + MONTHLY_TABLE_NAME);
			onCreate(budgetDB);
		}
	}

	/**
	 * Expense database object
	 */
	public static final class Expense {
		public long _ID;
		public String NAME;
		public String TYPE;
		public double AMOUNT;
		public String AMOUNT_TYPE;
	}

	/**
	 * Monthly item database object
	 */
	public static final class Monthly {
		public long _ID;
		public long EXPENSE_ID;
		public long CREDIT_ID;
		public long DUE_DATE;
		public double AMOUNT_PAID;
		public double BALANCE;
	}

	/**
	 * Credit database object
	 */
	public static final class Credit {
		public long _ID;
		public long CREDIT_DATE;
		public long AMOUNT;
	}
}
